﻿using System;
using System.Configuration;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using UniSync.Core;
using System.Data.SqlClient;
using Microsoft.Synchronization.Data.SqlServer;
using System.Data;

namespace UniSync.Tests
{
    [TestClass]
    public class AzureSyncTests
    {
        private static AzureSync _azureSync;
        private static string _localConnectionString;
        private static string _azureConnectionString;
        private const string _scopeName = "SyncUnitTestScope";
        private static readonly string[] _tableNamesToSync = new string[] { "TEST_SyncTable1", "TEST_SyncTable2" };

        [ClassInitialize]
        public static void TestClassInitialize(TestContext context)
        {
            _localConnectionString = ConfigurationManager.ConnectionStrings["LocalConnectionString"].ConnectionString;
            _azureConnectionString = ConfigurationManager.ConnectionStrings["CloudConnectionString"].ConnectionString;

            DropRecreateAndSetupSyncForTestTables();
        }

        private static void DropRecreateAndSetupSyncForTestTables()
        {
            DropTestSyncProvisioning(_localConnectionString);
            DropTestSyncProvisioning(_azureConnectionString);
            DropTestTables(_localConnectionString);
            DropTestTables(_azureConnectionString);

            CreateTestTables(_localConnectionString);
            CreateTestTables(_azureConnectionString);

            _azureSync = new AzureSync(_localConnectionString, _azureConnectionString, _scopeName, _tableNamesToSync);
            _azureSync.Setup();
        }

        [ClassCleanup]
        public static void TestClassCleanup()
        {
            DropTestTables(_localConnectionString);
            DropTestTables(_azureConnectionString);
            DropTestSyncProvisioning(_localConnectionString);
            DropTestSyncProvisioning(_azureConnectionString);
        }

        private static void CreateTestTables(string connectionString)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText =
                        @"CREATE TABLE TEST_SyncTable1
                          (
                             ID INT IDENTITY(1,1) NOT NULL,
                             Name NVARCHAR(100) NOT NULL,
                             Description NVARCHAR(MAX) NOT NULL,
                             CONSTRAINT [PK_TEST_SyncTable1] PRIMARY KEY CLUSTERED ([ID] ASC)
                          )";
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText =
                        @"CREATE TABLE TEST_SyncTable2
                          (
                             ID BIGINT IDENTITY(1,1) NOT NULL,
                             Name NVARCHAR(100) NOT NULL,
                             Description NVARCHAR(MAX) NOT NULL,
                             CONSTRAINT [PK_TEST_SyncTable2] PRIMARY KEY CLUSTERED ([ID] ASC)
                          )";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private static void DropTestTables(string connectionString)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEST_SyncTable1]') AND type in (N'U')) DROP TABLE TEST_SyncTable1";
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEST_SyncTable2]') AND type in (N'U')) DROP TABLE TEST_SyncTable2";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private static void ClearTestTables(IDbConnection connection)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"DELETE FROM TEST_SyncTable1";
                cmd.ExecuteNonQuery();
            }
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"DELETE FROM TEST_SyncTable2";
                cmd.ExecuteNonQuery();
            }
        }

        private static void DropTestSyncProvisioning(string connectionString)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var sqlServerProv = new SqlSyncScopeProvisioning(connection);
                var sqlServerDeprov = new SqlSyncScopeDeprovisioning(connection);
                
                if (sqlServerProv.ScopeExists(_scopeName))
                    sqlServerDeprov.DeprovisionScope(_scopeName);
            }
        }
    
        private int InsertIntoTEST_SyncTable1(IDbConnection connection, string name, string description)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"INSERT INTO TEST_SyncTable1 (Name, Description) VALUES (@Name, @Description); 
                                    SELECT @@IDENTITY";
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@Description", description));
                int id = Convert.ToInt32(cmd.ExecuteScalar());
                return id;
            }
        }

        private long InsertIntoTEST_SyncTable2(IDbConnection connection, string name, string description)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"INSERT INTO TEST_SyncTable2 (Name, Description) VALUES (@Name, @Description);
                                    SELECT @@IDENTITY";
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@Description", description));
                long id = Convert.ToInt64(cmd.ExecuteScalar());
                return id;
            }
        }

        private void UpdateTEST_SyncTable1(IDbConnection connection, int id, string name, string description)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "UPDATE TEST_SyncTable1 SET Name = @Name, Description = @Description WHERE ID = @ID";
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@Description", description));
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                cmd.ExecuteNonQuery();
            }
        }

        private void UpdateTEST_SyncTable2(IDbConnection connection, long id, string name, string description)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "UPDATE TEST_SyncTable2 SET Name = @Name, Description = @Description WHERE ID = @ID";
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@Description", description));
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                cmd.ExecuteNonQuery();
            }
        }

        private void DeleteFromTEST_SyncTable1(IDbConnection connection, int id)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM TEST_SyncTable1 WHERE ID = @ID";
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                cmd.ExecuteNonQuery();
            }
        }

        private void DeleteFromTEST_SyncTable2(IDbConnection connection, long id)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM TEST_SyncTable2 WHERE ID = @ID";
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                cmd.ExecuteNonQuery();
            }
        }

        private DataSet SelectFromTEST_SyncTable1(IDbConnection connection, int id)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM TEST_SyncTable1 WHERE ID = @ID";
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                using (var dataAdapter = new SqlDataAdapter((SqlCommand)cmd)) 
                {
                    var ds = new DataSet();
                    dataAdapter.Fill(ds);
                    return ds;
                }
            }
        }

        private DataSet SelectFromTEST_SyncTable1(IDbConnection connection)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM TEST_SyncTable1 ORDER BY ID ASC";
                using (var dataAdapter = new SqlDataAdapter((SqlCommand)cmd))
                {
                    var ds = new DataSet();
                    dataAdapter.Fill(ds);
                    return ds;
                }
            }
        }

        private DataSet SelectFromTEST_SyncTable2(IDbConnection connection, long id)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM TEST_SyncTable2 WHERE ID = @ID";
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                using (var dataAdapter = new SqlDataAdapter((SqlCommand)cmd))
                {
                    var ds = new DataSet();
                    dataAdapter.Fill(ds);
                    return ds;
                }
            }
        }

        private DataSet SelectFromTEST_SyncTable(IDbConnection connection)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM TEST_SyncTable2 ORDER BY ID ASC";
                using (var dataAdapter = new SqlDataAdapter((SqlCommand)cmd))
                {
                    var ds = new DataSet();
                    dataAdapter.Fill(ds);
                    return ds;
                }
            }
        }

        [TestMethod]
        public void CanSyncInsert_FromLocalToAzure()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    int id = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");

                    // act
                    _azureSync.Sync();

                    // assert
                    var dsLocal = SelectFromTEST_SyncTable1(localConnection, id);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection, id);

                    Assert.AreEqual(1, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsAzure.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalInsert1", dsLocal.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert1", dsLocal.Tables[0].Rows[0]["Description"]);

                    Assert.AreEqual("LocalInsert1", dsAzure.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert1", dsAzure.Tables[0].Rows[0]["Description"]);
                }
            }
        }

        [TestMethod]
        public void CanSyncInsert_FromLocalToAzure_OnMultipleTables()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    int id1 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    long id2 = InsertIntoTEST_SyncTable2(localConnection, "LocalInsert2", "LocalInsert2");

                    // act
                    _azureSync.Sync();

                    // assert
                    var dsLocal1 = SelectFromTEST_SyncTable1(localConnection, id1);
                    var dsLocal2 = SelectFromTEST_SyncTable2(localConnection, id2);
                    var dsAzure1 = SelectFromTEST_SyncTable1(azureConnection, id1);
                    var dsAzure2 = SelectFromTEST_SyncTable2(azureConnection, id2);

                    Assert.AreEqual(1, dsLocal1.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsLocal2.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsAzure1.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsAzure2.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalInsert1", dsLocal1.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert1", dsLocal1.Tables[0].Rows[0]["Description"]);
                    Assert.AreEqual("LocalInsert1", dsAzure1.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert1", dsAzure1.Tables[0].Rows[0]["Description"]);

                    Assert.AreEqual("LocalInsert2", dsLocal2.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert2", dsLocal2.Tables[0].Rows[0]["Description"]);
                    Assert.AreEqual("LocalInsert2", dsAzure2.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert2", dsAzure2.Tables[0].Rows[0]["Description"]);
                }
            }
        }

        [TestMethod]
        public void CanSyncUpdate_FromLocalToAzure()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    int id = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    _azureSync.Sync();
                    UpdateTEST_SyncTable1(localConnection, id, "LocalUpdate1", "LocalUpdate1");

                    // act
                    _azureSync.Sync();

                    // assert
                    var dsLocal = SelectFromTEST_SyncTable1(localConnection, id);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection, id);

                    Assert.AreEqual(1, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsAzure.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalUpdate1", dsLocal.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsLocal.Tables[0].Rows[0]["Description"]);

                    Assert.AreEqual("LocalUpdate1", dsAzure.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsAzure.Tables[0].Rows[0]["Description"]);
                }
            }
        }

        [TestMethod]
        public void CanSyncDelete_FromLocalToAzure()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();
                    
                    // arrange
                    int id = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    _azureSync.Sync();
                    DeleteFromTEST_SyncTable1(localConnection, id);

                    // act
                    _azureSync.Sync();

                    // assert
                    var dsLocal = SelectFromTEST_SyncTable1(localConnection, id);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection, id);

                    Assert.AreEqual(0, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(0, dsAzure.Tables[0].Rows.Count);
                }
            }
        }
        
        [TestMethod]
        public void CanSyncMultipleChanges_FromLocalToAzure()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    int id1 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    int id2 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert2", "LocalInsert2");
                    int id3 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert3", "LocalInsert3");

                    UpdateTEST_SyncTable1(localConnection, id1, "LocalUpdate1", "LocalUpdate1");
                    UpdateTEST_SyncTable1(localConnection, id2, "LocalUpdate2", "LocalUpdate2");
                    DeleteFromTEST_SyncTable1(localConnection, id3);
                    

                    // act
                    _azureSync.Sync();

                    // assert - First record was inserted then updated
                    var dsAzure1 = SelectFromTEST_SyncTable1(azureConnection, id1);

                    Assert.AreEqual(1, dsAzure1.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalUpdate1", dsAzure1.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsAzure1.Tables[0].Rows[0]["Description"]);

                    // assert - Second record was inserted then updated
                    var dsAzure2 = SelectFromTEST_SyncTable1(azureConnection, id2);
                    
                    Assert.AreEqual(1, dsAzure2.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalUpdate2", dsAzure2.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate2", dsAzure2.Tables[0].Rows[0]["Description"]);

                    // assert - Third record was inserted but then deleted
                    var dsAzure3 = SelectFromTEST_SyncTable1(azureConnection, id3);

                    Assert.AreEqual(0, dsAzure3.Tables[0].Rows.Count);
                }
            }
        }

        [TestMethod]
        public void CanSyncInsert_Bidirectional()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    ClearTestTables(localConnection);
                    ClearTestTables(azureConnection);

                    // act
                    int idLocal1 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    _azureSync.Sync();
                    
                    int idAzure1 = InsertIntoTEST_SyncTable1(azureConnection, "AzureInsert1", "AzureInsert1");
                    _azureSync.Sync();

                    // assert
                    var dsLocal = SelectFromTEST_SyncTable1(localConnection);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection);

                    Assert.AreEqual(2, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(2, dsAzure.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalInsert1", dsLocal.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert1", dsLocal.Tables[0].Rows[0]["Description"]);
                    Assert.AreEqual("AzureInsert1", dsLocal.Tables[0].Rows[1]["Name"]);
                    Assert.AreEqual("AzureInsert1", dsLocal.Tables[0].Rows[1]["Description"]);

                    Assert.AreEqual("LocalInsert1", dsAzure.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert1", dsAzure.Tables[0].Rows[0]["Description"]);
                    Assert.AreEqual("AzureInsert1", dsAzure.Tables[0].Rows[1]["Name"]);
                    Assert.AreEqual("AzureInsert1", dsAzure.Tables[0].Rows[1]["Description"]);
                }
            }
        }

        [TestMethod]
        public void CanSyncUpdate_Bidirectional()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    ClearTestTables(localConnection);
                    ClearTestTables(azureConnection);

                    int id1 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    int id2 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert2", "LocalInsert2");
                    _azureSync.Sync();

                    // act
                    UpdateTEST_SyncTable1(localConnection, id1, "LocalUpdate1", "LocalUpdate1");
                    UpdateTEST_SyncTable1(azureConnection, id2, "AzureUpdate2", "AzureUpdate2");
                    _azureSync.Sync();

                    // assert - row 0 should have local update, row 1 should have azure update
                    var dsLocal = SelectFromTEST_SyncTable1(localConnection);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection);

                    Assert.AreEqual(2, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(2, dsAzure.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalUpdate1", dsLocal.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsLocal.Tables[0].Rows[0]["Description"]);
                    Assert.AreEqual("AzureUpdate2", dsLocal.Tables[0].Rows[1]["Name"]);
                    Assert.AreEqual("AzureUpdate2", dsLocal.Tables[0].Rows[1]["Description"]);

                    Assert.AreEqual("LocalUpdate1", dsAzure.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsAzure.Tables[0].Rows[0]["Description"]);
                    Assert.AreEqual("AzureUpdate2", dsAzure.Tables[0].Rows[1]["Name"]);
                    Assert.AreEqual("AzureUpdate2", dsAzure.Tables[0].Rows[1]["Description"]);
                }
            }
        }

        [TestMethod]
        public void CanSyncDelete_Bidirectional()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    ClearTestTables(localConnection);
                    ClearTestTables(azureConnection);

                    int id1 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    int id2 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert2", "LocalInsert2");
                    int id3 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert3", "LocalInsert3");
                    _azureSync.Sync();

                    // act
                    // Delete one record locally, one in Azure, and keep the third one
                    DeleteFromTEST_SyncTable1(localConnection, id1);
                    DeleteFromTEST_SyncTable1(azureConnection, id2);
                    _azureSync.Sync();

                    // assert - only id3 remains, the others were deleted in both databases
                    var dsLocal = SelectFromTEST_SyncTable1(localConnection);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection);
                    
                    Assert.AreEqual(1, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsAzure.Tables[0].Rows.Count);
                    
                    Assert.AreEqual("LocalInsert3", dsLocal.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert3", dsLocal.Tables[0].Rows[0]["Description"]);

                    Assert.AreEqual("LocalInsert3", dsAzure.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert3", dsAzure.Tables[0].Rows[0]["Description"]);
                }
            }
        }

        [TestMethod]
        public void CanSyncInsert_Bidirectional_WithConflict()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    // Drop and recreate test tables. Simply clearing the data is not enough for this test
                    // because we want to ensure the generated IDs are the same in both tables
                    // (simply using DBCC CHECKIDENT would not work with SQL Azure)
                    DropRecreateAndSetupSyncForTestTables();

                    // insert a record with the same ID in both databases
                    int idLocal1 = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    int idAzure1 = InsertIntoTEST_SyncTable1(azureConnection, "AzureInsert1", "AzureInsert1");
                    Assert.AreEqual(idLocal1, idAzure1, "IDs must be the same in both database to have a conflict");

                    var localConflict = false;
                    var azureConflict = false;
                    _azureSync.LocalApplyChangeFailed += (s, e) => localConflict = true;
                    _azureSync.AzureApplyChangeFailed += (s, e) => azureConflict = true;

                    // act
                    _azureSync.Sync();

                    // assert - local change overrides Azure changes
                    // ********** TODO: Is this really what we want? **********
                    var dsLocal = SelectFromTEST_SyncTable1(localConnection);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection);

                    Assert.IsFalse(localConflict, "No conflict should be found in the local database, the local version should override the azure version by default");
                    Assert.IsTrue(azureConflict, "Expect a conflict to be found in the Azure database, the local version should override the azure version by default");

                    Assert.AreEqual(1, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsAzure.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalInsert1", dsLocal.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert1", dsLocal.Tables[0].Rows[0]["Description"]);

                    Assert.AreEqual("LocalInsert1", dsAzure.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalInsert1", dsAzure.Tables[0].Rows[0]["Description"]);
                }
            }
        }

        [TestMethod]
        public void CanSyncUpdate_Bidirectional_WithConflict()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    ClearTestTables(localConnection);
                    ClearTestTables(azureConnection);

                    var localConflict = false;
                    var azureConflict = false;
                    _azureSync.LocalApplyChangeFailed += (s, e) => localConflict = true;
                    _azureSync.AzureApplyChangeFailed += (s, e) => azureConflict = true;

                    int id = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    _azureSync.Sync();

                    // act - update the same record in both databases, with different values
                    UpdateTEST_SyncTable1(localConnection, id, "LocalUpdate1", "LocalUpdate1");
                    UpdateTEST_SyncTable1(azureConnection, id, "AzureUpdate1", "AzureUpdate1");
                    _azureSync.Sync();

                    // assert - local change overrides Azure changes
                    // ********** TODO: Is this really what we want? **********
                    Assert.IsFalse(localConflict, "No conflict should be found in the local database, the local version should override the azure version by default");
                    Assert.IsTrue(azureConflict, "Expect a conflict to be found in the Azure database, the local version should override the azure version by default");

                    var dsLocal = SelectFromTEST_SyncTable1(localConnection);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection);

                    Assert.AreEqual(1, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsAzure.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalUpdate1", dsLocal.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsLocal.Tables[0].Rows[0]["Description"]);
                    
                    Assert.AreEqual("LocalUpdate1", dsAzure.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsAzure.Tables[0].Rows[0]["Description"]);
                }
            }
        }

        [TestMethod]
        public void CanSyncInsertThenDelete_Bidirectional_WithConflict()
        {
            using (var localConnection = new SqlConnection(_localConnectionString))
            {
                localConnection.Open();
                using (var azureConnection = new SqlConnection(_azureConnectionString))
                {
                    azureConnection.Open();

                    // arrange
                    ClearTestTables(localConnection);
                    ClearTestTables(azureConnection);

                    var localConflict = false;
                    var azureConflict = false;
                    _azureSync.LocalApplyChangeFailed += (s, e) => localConflict = true;
                    _azureSync.AzureApplyChangeFailed += (s, e) => azureConflict = true;

                    int id = InsertIntoTEST_SyncTable1(localConnection, "LocalInsert1", "LocalInsert1");
                    _azureSync.Sync();

                    // act - delete a record locally, update it in Azure
                    UpdateTEST_SyncTable1(localConnection, id, "LocalUpdate1", "LocalUpdate1");
                    DeleteFromTEST_SyncTable1(azureConnection, id);
                    _azureSync.Sync();

                    // assert - local change overrides Azure changes (so the record is "undeleted" in Azure)
                    // ********** TODO: Is this really what we want? **********
                    Assert.IsFalse(localConflict, "No conflict should be found in the local database, the local version should override the azure version by default");
                    Assert.IsTrue(azureConflict, "Expect a conflict to be found in the Azure database, the local version should override the azure version by default");

                    var dsLocal = SelectFromTEST_SyncTable1(localConnection);
                    var dsAzure = SelectFromTEST_SyncTable1(azureConnection);

                    Assert.AreEqual(1, dsLocal.Tables[0].Rows.Count);
                    Assert.AreEqual(1, dsAzure.Tables[0].Rows.Count);

                    Assert.AreEqual("LocalUpdate1", dsLocal.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsLocal.Tables[0].Rows[0]["Description"]);

                    Assert.AreEqual("LocalUpdate1", dsAzure.Tables[0].Rows[0]["Name"]);
                    Assert.AreEqual("LocalUpdate1", dsAzure.Tables[0].Rows[0]["Description"]);
                }
            }
        }
    }
}
